﻿using System;
using System.Data;
using System.Collections.Generic;
using CounterServerModel.Exceptions;
using CounterServerModel.Authentication;

namespace CounterServerModel
{
    /// <summary>
    /// A class for billing operations.
    /// </summary>
    public class BillingService
    {
        private AuthenticationService m_AuthenticationService;

        public BillingService()
        {
            m_AuthenticationService = new AuthenticationService();
        }

        /// <summary>
        /// Returns the monthly usage of a given account in the current month so far.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_AccountID">The ID of the account the monthly usage is returned for.</param>
        /// <returns>The monthly usage of a given account in the current month so far.</returns>
        public double GetMonthlyUsageSoFar(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AccountID)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            bool allowOperation = false;

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if ((userPermissions & eUserPermissions.ReadOtherBill) == eUserPermissions.ReadOtherBill)
            {
                allowOperation = true;
            }
            else
            {
                DataTable tableOfAccountsOfUser = DBService.Instance.PerformQuery(
                    string.Format(
                    "SELECT AccountID FROM [Accounts-Users] WHERE UserID = '{0}'",
                    i_AuthenticatedUserCredentials.ID));

                bool isAssociated = false;
                for (int i = 0; (i < tableOfAccountsOfUser.Rows.Count) && !isAssociated; i++)
                {
                    if (int.Parse(tableOfAccountsOfUser.Rows[i]["AccountID"].ToString()) == i_AccountID)
                    {
                        isAssociated = true;
                    }
                }

                if (isAssociated && ((userPermissions & eUserPermissions.ReadOwnBill) == eUserPermissions.ReadOwnBill))
                {
                    allowOperation = true;
                }
            }

            if (!allowOperation)
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

            return GetUsageOfMonth(i_AccountID, DateTime.Now.Year, DateTime.Now.Month);
        }

        /// <summary>
        /// Returns a DataTable of the monthly usage of a given account.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_AccountID">The ID of the Account to return usage for.</param>
        /// <param name="i_FromYear">The beginning year.</param>
        /// <param name="i_FromMonth">The beginning month.</param>
        /// <param name="i_FromDay">The beginning day.</param>
        /// <param name="i_ToYear">The end year.</param>
        /// <param name="i_ToMonth">The end month.</param>
        /// <param name="i_ToDay">The end day.</param>
        /// <returns>A DataTable of monthly usage of a given account.</returns>
        public DataTable GetUsagesTable(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AccountID, int i_FromYear, int i_FromMonth, int i_FromDay, int i_ToYear, int i_ToMonth, int i_ToDay)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            bool allowOperation = false;

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if ((userPermissions & eUserPermissions.ReadOtherBill) == eUserPermissions.ReadOtherBill)
            {
                allowOperation = true;
            }
            else
            {
                DataTable tableOfAccountsOfUser = DBService.Instance.PerformQuery(
                    string.Format(
                    "SELECT AccountID FROM [Accounts-Users] WHERE UserID = '{0}'",
                    i_AuthenticatedUserCredentials.ID));

                bool isAssociated = false;
                for (int i = 0; (i < tableOfAccountsOfUser.Rows.Count) && !isAssociated; i++)
                {
                    if (int.Parse(tableOfAccountsOfUser.Rows[i]["AccountID"].ToString()) == i_AccountID)
                    {
                        isAssociated = true;
                    }
                }

                if (isAssociated && ((userPermissions & eUserPermissions.ReadOwnBill) == eUserPermissions.ReadOwnBill))
                {
                    allowOperation = true;
                }
            }

            if (!allowOperation)
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

            MonthlyUsage usages = new MonthlyUsage(i_AccountID);

            DateTime firstDay = new DateTime(i_FromYear, i_FromMonth, i_FromDay, 0, 0, 0);
            DateTime lastDay = new DateTime(i_ToYear, i_ToMonth, i_ToDay, 0, 0, 0);
            DateTime dayAfterTheLastDay = lastDay.AddDays(1);

            string selectString = string.Format("SELECT DISTINCT AccountID, UpdateDate, TotalUsage FROM Usages WHERE AccountID = '{0}' AND UpdateDate >= '{1}' AND UpdateDate < '{2}' ORDER BY UpdateDate ASC;", i_AccountID, firstDay.ToString("yyyy/MM/dd 00:00:00"), dayAfterTheLastDay.ToString("yyyy/MM/dd 00:00:00"));
            DataTable monthUsages = DBService.Instance.PerformQuery(selectString);

            return monthUsages;
        }

        /// <summary>
        /// Returns a monthly prediction of the usage of a given Account.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_AccountID">The ID of the Account to create prediction for.</param>
        /// <returns>A monthly prediction of the usage of a given Account.</returns>
        public MonthlyUsage GetUsagePredictionForOneMonth(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AccountID)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            bool allowOperation = false;

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if ((userPermissions & eUserPermissions.ReadOtherAccountInfo) == eUserPermissions.ReadOtherAccountInfo)
            {
                allowOperation = true;
            }
            else
            {
                DataTable tableOfAccountsOfUser = DBService.Instance.PerformQuery(
                    string.Format(
                    "SELECT AccountID FROM [Accounts-Users] WHERE UserID = '{0}'",
                    i_AuthenticatedUserCredentials.ID));

                bool isAssociated = false;
                for (int i = 0; (i < tableOfAccountsOfUser.Rows.Count) && !isAssociated; i++)
                {
                    if (int.Parse(tableOfAccountsOfUser.Rows[i]["AccountID"].ToString()) == i_AccountID)
                    {
                        isAssociated = true;
                    }
                }

                if (isAssociated && ((userPermissions & eUserPermissions.ReadAssociatedAccountInfo) == eUserPermissions.ReadAssociatedAccountInfo))
                {
                    allowOperation = true;
                }
            }

            if (!allowOperation)
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

            string selectString = string.Format("SELECT TOP 200 UpdateDate, TotalUsage FROM Usages WHERE AccountID = '{0}' ORDER BY UpdateDate DESC;", i_AccountID);
            DataTable usageTable = DBService.Instance.PerformQuery(selectString);
            double sumOfDeltas = 0;

            MonthlyUsage prediction = new MonthlyUsage(i_AccountID);

            if (usageTable.Rows.Count == 0)
            {
                return prediction;
            }

            double previousUsage = double.Parse(usageTable.Rows[0]["TotalUsage"].ToString());

            // We do not start from the first element because the difference (delta) can be calculated only from the second element.
            for (int i = 1; i < usageTable.Rows.Count; i++)
            {
                double currentUsage = double.Parse(usageTable.Rows[i]["TotalUsage"].ToString());
                sumOfDeltas += previousUsage - currentUsage;
                previousUsage = currentUsage;
            }

            double averageDelta = sumOfDeltas / usageTable.Rows.Count;
            double predictedUsage = double.Parse(usageTable.Rows[0]["TotalUsage"].ToString());
            DateTime predictedUsageDate = DateTime.Parse(usageTable.Rows[0]["UpdateDate"].ToString());

            for (int i = 0; i < 5000; i++)
            {
                predictedUsage += averageDelta;
                predictedUsageDate = predictedUsageDate.AddMinutes(9);
                prediction.AddUsage(predictedUsageDate, predictedUsage);
            }

            return prediction;
        }

        /// <summary>
        /// Creates a bill for the given account for the given year and month.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_AccountID">The ID of the account the bill is created for.</param>
        /// <param name="i_Year">The year the bill is created for.</param>
        /// <param name="i_Month">The month the bill is created for.</param>
        /// <returns>A bill for the given account for the given year and month.</returns>
        public Bill CreateBill(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AccountID, int i_Year, int i_Month)
        {
            Bill bill = new Bill();
			bill = GetBillPrediction(i_AuthenticatedUserCredentials, i_AccountID, i_Year, i_Month);

			DataTable prevousBills = DBService.Instance.PerformQuery(string.Format(
				"SELECT AccountID, PaymentYear, PaymentMonth FROM Bills WHERE AccountID={0} AND PaymentYear={1} AND PaymentMonth={2};",
				i_AccountID,
				i_Year,
				i_Month));

			if (prevousBills.Rows.Count != 0)
			{
				throw new BillAlreadyExsistsException(i_AccountID, i_Month, i_Month);
			}

            string commandString = string.Format(
                "INSERT INTO Bills (PaymentYear, PaymentMonth, AccountID, TotalUsage, SumPaid) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}');",
                bill.Year,
                bill.Month,
                bill.AccountID,
                bill.TotalUsage,
                bill.SumPaid);

            DBService.Instance.ExecuteCommand(commandString);

            return bill;
        }

		/// <summary>
		/// get a prediction for a bill for the given account for the given year and month.
		/// </summary>
		/// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
		/// <param name="i_AccountID">The ID of the account the bill is created for.</param>
		/// <param name="i_Year">The year the bill is created for.</param>
		/// <param name="i_Month">The month the bill is created for.</param>
		/// <returns>A bill prediction for the given account for the given year and month.</returns>
		public Bill GetBillPrediction(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AccountID, int i_Year, int i_Month)
		{
			m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

			DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

			eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

			if (!((userPermissions & eUserPermissions.CreateEditBill) == eUserPermissions.CreateEditBill))
			{
				throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
			}

			double usage = GetUsageOfMonth(i_AccountID, i_Year, i_Month);
			DataTable billingTable = DBService.Instance.PerformQuery("SELECT StartDate, PriceForLiter, Comments FROM BillingProperties WHERE EndDate IS NULL;");
			if (billingTable.Rows.Count == 0)
			{
				throw new PriceNotSetException();
			}

			double price = double.Parse(billingTable.Rows[0]["PriceForLiter"].ToString());

			Bill bill = new Bill();
			bill.AccountID = i_AccountID;
			bill.Year = i_Year;
			bill.Month = i_Month;
			bill.TotalUsage = GetTotalUsageTillEndOfMonth(i_AccountID, i_Year, i_Month);
			bill.SumPaid = usage * price;

			

			return bill;
		}


        /// <summary>
        /// Returns the bill for the given account for the given year and month.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_AccountID">The ID of the account the bill is returned for.</param>
        /// <param name="i_Year">The year the bill is returned for.</param>
        /// <param name="i_Month">The month the bill is returned for.</param>
        /// <returns>The bill for the given account for the given year and month.</returns>
        public Bill GetBill(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AccountID, int i_Year, int i_Month)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            bool allowOperation = false;

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if ((userPermissions & eUserPermissions.ReadOtherBill) == eUserPermissions.ReadOtherBill)
            {
                allowOperation = true;
            }
            else
            {
                DataTable tableOfAccountsOfUser = DBService.Instance.PerformQuery(
                    string.Format(
                    "SELECT AccountID FROM [Accounts-Users] WHERE UserID = '{0}'",
                    i_AuthenticatedUserCredentials.ID));

                bool isAssociated = false;
                for (int i = 0; (i < tableOfAccountsOfUser.Rows.Count) && !isAssociated; i++)
                {
                    if (int.Parse(tableOfAccountsOfUser.Rows[i]["AccountID"].ToString()) == i_AccountID)
                    {
                        isAssociated = true;
                    }
                }

                if (isAssociated && ((userPermissions & eUserPermissions.ReadOwnBill) == eUserPermissions.ReadOwnBill))
                {
                    allowOperation = true;
                }
            }

            if (!allowOperation)
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

            string selectString = string.Format(
                "SELECT PaymentYear, PaymentMonth, AccountID, TotalUsage, SumPaid FROM Bills WHERE PaymentYear = '{0}' AND PaymentMonth = '{1}' AND AccountID = '{2}';",
                i_Year,
                i_Month,
                i_AccountID);

            DataTable billsTable = DBService.Instance.PerformQuery(selectString);

            Bill bill = null;

            if (billsTable.Rows.Count != 0)
            {
                bill = new Bill();

                bill.Year = int.Parse(billsTable.Rows[0]["PaymentYear"].ToString());
                bill.Month = int.Parse(billsTable.Rows[0]["PaymentMonth"].ToString());
                bill.AccountID = int.Parse(billsTable.Rows[0]["AccountID"].ToString());
                bill.TotalUsage = double.Parse(billsTable.Rows[0]["TotalUsage"].ToString());
                bill.SumPaid = double.Parse(billsTable.Rows[0]["SumPaid"].ToString());
            }

            return bill;
        }

        /// <summary>
        /// Returns the current price for one liter.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <returns>The current price for one liter.</returns>
        public BillingInfo GetCurrentPrice(AuthenticatedUserCredentials i_AuthenticatedUserCredentials)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if (!((userPermissions & eUserPermissions.ReadBillingInfo) == eUserPermissions.ReadBillingInfo))
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

            DataTable billingTable = DBService.Instance.PerformQuery("SELECT StartDate, PriceForLiter, Comments FROM BillingProperties WHERE EndDate IS NULL;");

            BillingInfo billingInfo = new BillingInfo();
            billingInfo.StartDate = DateTime.Parse(billingTable.Rows[0]["StartDate"].ToString());
            billingInfo.EndDate = null;
            billingInfo.PriceForLiter = double.Parse(billingTable.Rows[0]["PriceForLiter"].ToString());
            billingInfo.Comments = billingTable.Rows[0]["Comments"].ToString();

            return billingInfo;
        }

		 /// <summary>
        /// Returns the billing info table.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <returns>The billing info history in a DataTable object.</returns>
        public DataTable GetBillingInfoTable(AuthenticatedUserCredentials i_AuthenticatedUserCredentials)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if (!((userPermissions & eUserPermissions.ReadBillingInfo) == eUserPermissions.ReadBillingInfo))
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

			DataTable billingTable = DBService.Instance.PerformQuery("SELECT StartDate, EndDate, PriceForLiter, Comments FROM BillingProperties ORDER BY StartDate DESC;");

			return billingTable;
        }

		/// <summary>
		/// Returns the billing info table.
		/// </summary>
		/// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
		/// <returns>The billing info history in a DataTable object.</returns>
		public DataTable GetBillingHistoryTable(AuthenticatedUserCredentials i_AuthenticatedUserCredentials)
		{
			m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

			DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

			eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

			if (!((userPermissions & eUserPermissions.ReadBillingInfo) == eUserPermissions.ReadBillingInfo))
			{
				throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
			}

			DataTable billingTable = DBService.Instance.PerformQuery(
				@"SELECT B.[PaymentYear]
						,B.[PaymentMonth]
						,B.[AccountID]
						,A.[Country]
						,A.[City]
						,A.[Street]
						,A.[House]
						,A.[Apartment]
						,U.[Username]
						,U.[Email]
						,B.[TotalUsage]
						,B.[SumPaid]
				FROM [Bills] as B
				INNER JOIN [Accounts-Users] as AU on (AU.AccountID = B.AccountID)
				INNER JOIN [Accounts] as A on (A.AccountID = AU.AccountID)
				INNER JOIN [Users] as U on (AU.UserID = U.UserID);"
				);

			return billingTable;
		}

        /// <summary>
        /// Sets the current price for one liter.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_Comments">Comments for the price.</param>
        public void SetCurrentPrice(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, double i_PriceForLiter, string i_Comments)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if (!((userPermissions & eUserPermissions.WriteBillingInfo) == eUserPermissions.WriteBillingInfo))
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

            DateTime currentDateTime = DateTime.Now;

            string commandString = string.Format("UPDATE BillingProperties SET EndDate = '{0}' WHERE EndDate IS NULL;", currentDateTime.ToString("yyyy/MM/dd hh:mm:ss:mmm"));
            DBService.Instance.ExecuteCommand(commandString);

            commandString = string.Format(
                "INSERT INTO BillingProperties (StartDate, PriceForLiter, Comments) VALUES ('{0}', '{1}', '{2}');",
				currentDateTime.ToString("yyyy/MM/dd hh:mm:ss:mmm"),
                i_PriceForLiter,
                i_Comments);
            DBService.Instance.ExecuteCommand(commandString);
        }

        /// <summary>
        /// Returns true iff a price is set.
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <returns>True iff a price is set.</returns>
        public bool IsPriceSet(AuthenticatedUserCredentials i_AuthenticatedUserCredentials)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if (!((userPermissions & eUserPermissions.ReadBillingInfo) == eUserPermissions.ReadBillingInfo))
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }

            DataTable billingTable = DBService.Instance.PerformQuery("SELECT PriceForLiter FROM BillingProperties WHERE EndDate IS NULL;");

            return (billingTable.Rows.Count > 0);
        }

        /// <summary>
        /// Get Bill Table Per Acount(with range selection).
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_AcountID">Acount identification.</param>
        /// <param name="i_fromYear">To year selection.</param>
        /// <param name="i_fromMonth">From month selection.</param>
        /// <param name="i_toYear">To Year selection.</param>
        /// <param name="i_toMonth">To month selection.</param>
        public DataTable GetBillsTablePerAcount(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AcountID, int i_fromYear, int i_fromMonth, int i_toYear, int i_toMonth)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());

            if (!((userPermissions & eUserPermissions.ReadOwnBill) == eUserPermissions.ReadOwnBill))
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }
            DataTable billsPerAcountTable = DBService.Instance.PerformQuery(
                 string.Format("SELECT B.[PaymentYear] as 'Year',B.[PaymentMonth] as 'Month',B.[TotalUsage] as 'Total Usage',B.[SumPaid] as 'Sum Paid' FROM [Bills] as B  WHERE B.AccountID ={0} and B.PaymentYear >= {1} and B.PaymentYear <= {2} and B.PaymentMonth >= {3} and B.PaymentMonth <= {4} ", i_AcountID, i_fromYear, i_toYear, i_fromMonth, i_toMonth));

            return billsPerAcountTable;
        }

        /// <summary>
        /// Get Bill Table Per Acount(all bills for acount).
        /// </summary>
        /// <param name="i_AuthenticatedUserCredentials">The credentials of the user performing the operation.</param>
        /// <param name="i_AcountID">Acount identification.</param>

        public DataTable GetBillsTablePerAcount(AuthenticatedUserCredentials i_AuthenticatedUserCredentials, int i_AcountID)
        {
            m_AuthenticationService.ValidateAuthentication(i_AuthenticatedUserCredentials);

            DataRow rowOfSearchingUser = GetRowOfUser(i_AuthenticatedUserCredentials.ID);

            eUserPermissions userPermissions = (eUserPermissions)int.Parse(rowOfSearchingUser["Permissions"].ToString());
            if (!((userPermissions & eUserPermissions.ReadOwnBill) == eUserPermissions.ReadOwnBill))
            {
                throw new NotEnoughPrivilagesException(i_AuthenticatedUserCredentials.ID);
            }
            DataTable billsPerAcountTable = DBService.Instance.PerformQuery(
                 string.Format("SELECT B.[PaymentYear],B.[PaymentMonth],B.[TotalUsage],B.[SumPaid] FROM [Bills] as B  WHERE B.AccountID ={0} ", i_AcountID));

            return billsPerAcountTable;
        }

        private DataRow GetRowOfUser(int i_UserID)
        {
            DataTable table = DBService.Instance.PerformQuery(string.Format("SELECT UserID, Username, Permissions, Phone, MobilePhone, Email, Password FROM Users WHERE UserID = '{0}'", i_UserID));

            if (table.Rows.Count == 0)
            {
                throw new UserIDNotExistsException(i_UserID);
            }

            return table.Rows[0];
        }

        private double GetUsageOfMonth(int i_AccountID, int i_Year, int i_Month)
        {
            double usage = 0;

            DateTime firstDayOfMonth = new DateTime(i_Year, i_Month, 1, 0, 0, 0);
            DateTime firstDayOfNextMonth = firstDayOfMonth.AddMonths(1);
            DateTime firstDayOfPreviousMonth = firstDayOfMonth.AddMonths(-1);

            string selectString = string.Format("SELECT TotalUsage FROM Usages WHERE UpdateDate = (SELECT MAX(UpdateDate) FROM Usages WHERE AccountID = '{0}' AND UpdateDate >= '{1}' AND UpdateDate < '{2}');", i_AccountID, firstDayOfPreviousMonth, firstDayOfMonth);
            DataTable previousMonthUsages = DBService.Instance.PerformQuery(selectString);

            selectString = string.Format("SELECT TotalUsage FROM Usages WHERE UpdateDate = (SELECT MAX(UpdateDate) FROM Usages WHERE AccountID = '{0}' AND UpdateDate >= '{1}' AND UpdateDate < '{2}');", i_AccountID, firstDayOfMonth, firstDayOfNextMonth);
            DataTable monthUsages = DBService.Instance.PerformQuery(selectString);

            if (previousMonthUsages.Rows.Count == 0)
            {
                if (monthUsages.Rows.Count != 0)
                {
                    usage = double.Parse(monthUsages.Rows[0]["TotalUsage"].ToString());
                }
            }
            else
            {
                if (monthUsages.Rows.Count != 0)
                {
                    usage = double.Parse(monthUsages.Rows[0]["TotalUsage"].ToString()) - double.Parse(previousMonthUsages.Rows[0]["TotalUsage"].ToString());
                }
            }

            return usage;
        }

        private double GetTotalUsageTillEndOfMonth(int i_AccountID, int i_Year, int i_Month)
        {
            double usage = 0;

            DateTime firstDayOfMonth = new DateTime(i_Year, i_Month, 1, 0, 0, 0);
            DateTime firstDayOfNextMonth = firstDayOfMonth.AddMonths(1);

            string  selectString = string.Format("SELECT TotalUsage FROM Usages WHERE UpdateDate = (SELECT MAX(UpdateDate) FROM Usages WHERE AccountID = '{0}' AND UpdateDate >= '{1}' AND UpdateDate < '{2}');", i_AccountID, firstDayOfMonth, firstDayOfNextMonth);
            DataTable monthUsages = DBService.Instance.PerformQuery(selectString);
           
            if (monthUsages.Rows.Count != 0)
            {
                usage = double.Parse(monthUsages.Rows[0]["TotalUsage"].ToString());
            }

            return usage;
        }
    }
}
